/**
 * 
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import model.CTGDM;
import model.KhoiKienThucM;
import model.LoaiKhoiKienThucM;
import util.DAL;
import util.MyConnectUnit;

public class KhoiKienThucD {
	private MyConnectUnit conn;
	
	/**
	 * @function constructor KhoiKienThucD
	 * @throws Exception 
	 */
	public KhoiKienThucD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}

	/**
	 * @function get all KhoiKienThuc
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAlls(String ten, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<KhoiKienThucM> ls = new ArrayList<KhoiKienThucM>();
		ResultSet rs = this.conn.Selects(
						"id_khoi_kien_thuc, kkt.ten, so_tc_bb, so_tc_tc, so_tc_dk, kkt.id_ctgd, ctgd.ten_day_du, kkt.id_loai_khoi_kien_thuc, lkkt.ten",
						"KHOI_KIEN_THUC kkt, LOAI_KHOI_KIEN_THUC lkkt, CTGD ctgd",
						"kkt.id_loai_khoi_kien_thuc = lkkt.id_loai_khoi_kien_thuc AND kkt.id_ctgd = ctgd.id_ctgd AND ctgd.ten_day_du LIKE '%" + ten +"%'",
						"id_khoi_kien_thuc ASC",
						limit);
		KhoiKienThucM l = null;
		while (rs.next()) {
			l = new KhoiKienThucM();
			l.setId_khoi_kien_thuc(rs.getInt("id_khoi_kien_thuc"));
			l.setTen(rs.getString("kkt.ten"));
			l.setSo_tc_bb(rs.getInt("so_tc_bb"));
			l.setSo_tc_tc(rs.getInt("so_tc_tc"));
			l.setSo_tc_dk(rs.getInt("so_tc_dk"));
			
			l.setId_ctgd(rs.getInt("kkt.id_ctgd"));
			LoaiKhoiKienThucM lkkt = new LoaiKhoiKienThucM();
			lkkt.setTen(rs.getString("lkkt.ten"));
			l.setTen_lkkt(lkkt);
			
			l.setId_loai_khoi_kien_thuc(rs.getInt("kkt.id_loai_khoi_kien_thuc"));
			CTGDM ctgd = new CTGDM();
			ctgd.setTen_day_du(rs.getString("ctgd.ten_day_du"));
			l.setTen_day_du(ctgd);
			
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all KhoiKienThuc
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAlls(int id, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<KhoiKienThucM> ls = new ArrayList<KhoiKienThucM>();
		ResultSet rs = this.conn.Selects(
						"id_khoi_kien_thuc, kkt.ten, so_tc_bb, so_tc_tc, so_tc_dk, kkt.id_ctgd, ctgd.ten_day_du, kkt.id_loai_khoi_kien_thuc, lkkt.ten",
						"KHOI_KIEN_THUC kkt, LOAI_KHOI_KIEN_THUC lkkt, CTGD ctgd",
						"kkt.id_loai_khoi_kien_thuc = lkkt.id_loai_khoi_kien_thuc AND kkt.id_ctgd = ctgd.id_ctgd AND ctgd.id_ctgd = " + id,
						"id_khoi_kien_thuc ASC",
						limit);
		KhoiKienThucM l = null;
		while (rs.next()) {
			l = new KhoiKienThucM();
			l.setId_khoi_kien_thuc(rs.getInt("id_khoi_kien_thuc"));
			l.setTen(rs.getString("kkt.ten"));
			l.setSo_tc_bb(rs.getInt("so_tc_bb"));
			l.setSo_tc_tc(rs.getInt("so_tc_tc"));
			l.setSo_tc_dk(rs.getInt("so_tc_dk"));
			
			l.setId_ctgd(rs.getInt("kkt.id_ctgd"));
			LoaiKhoiKienThucM lkkt = new LoaiKhoiKienThucM();
			lkkt.setTen(rs.getString("lkkt.ten"));
			l.setTen_lkkt(lkkt);
			
			l.setId_loai_khoi_kien_thuc(rs.getInt("kkt.id_loai_khoi_kien_thuc"));
			CTGDM ctgd = new CTGDM();
			ctgd.setTen_day_du(rs.getString("ctgd.ten_day_du"));
			l.setTen_day_du(ctgd);
			
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all KhoiKienThuc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAll(String Condition, String Limit, String OrderBy) throws Exception {
		ArrayList<KhoiKienThucM> ls = new ArrayList<KhoiKienThucM>();
		ResultSet rs = this.conn.Select("KHOI_KIEN_THUC", Condition, Limit, OrderBy);
		KhoiKienThucM l = null;
		while (rs.next()) {
			l = new KhoiKienThucM();
			l.setId_khoi_kien_thuc(rs.getInt("id_khoi_kien_thuc"));
			l.setTen(rs.getString("ten"));
			l.setSo_tc_bb(rs.getInt("so_tc_bb"));
			l.setSo_tc_tc(rs.getInt("so_tc_tc"));
			l.setSo_tc_dk(rs.getInt("so_tc_dk"));
			l.setId_ctgd(rs.getInt("id_ctgd"));
			l.setId_loai_khoi_kien_thuc(rs.getInt("id_loai_khoi_kien_thuc"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all KhoiKienThuc with Condition + Limit
	 * @param Condition
	 * @param Limit
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAll(String Condition, String Limit) throws Exception {
		return getAll(Condition, Limit, null);
	}

	/**
	 * @function get all KhoiKienThuc with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}
	
	/**
	 * @function get all KhoiKienThuc no parameter
	 * @return
	 * @throws Exception
	 */
	public ArrayList<KhoiKienThucM> getAll() throws Exception {
		return getAll(null);
	}


	/**
	 * function insert KhoiKienThuc
	 * @param kkt
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(KhoiKienThucM kkt) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("ten", kkt.getTen());
		map.put("so_tc_bb", kkt.getSo_tc_bb());
		map.put("so_tc_tc", kkt.getSo_tc_tc());
		map.put("so_tc_dk", kkt.getSo_tc_dk());
		map.put("id_ctgd", kkt.getId_ctgd());
		map.put("id_loai_khoi_kien_thuc", kkt.getId_loai_khoi_kien_thuc());
		return this.conn.Insert("KHOI_KIEN_THUC", map);
	}

	/**
	 * @function update KhoiKienThuc
	 * @param kkt
	 * @return
	 * @throws Exception
	 */
	public boolean Update(KhoiKienThucM kkt) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("ten", kkt.getTen());
		map.put("so_tc_bb", kkt.getSo_tc_bb());
		map.put("so_tc_tc", kkt.getSo_tc_tc());
		map.put("so_tc_dk", kkt.getSo_tc_dk());
		map.put("id_ctgd", kkt.getId_ctgd());
		map.put("id_loai_khoi_kien_thuc", kkt.getId_loai_khoi_kien_thuc());
		return this.conn.Update("KHOI_KIEN_THUC", map, "id_khoi_kien_thuc = " + kkt.getId_khoi_kien_thuc());
	}

	/**
	 * @function delete KhoiKienThuc
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id_khoi_kien_thuc) throws Exception {
		return this.conn.Delete("KHOI_KIEN_THUC", "id_khoi_kien_thuc = " + id_khoi_kien_thuc);
	}
}